package sdu.attendancechecker.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import sdu.attendancechecker.dto.Question;
import sdu.attendancechecker.dto.Teacher;
import sdu.attendancechecker.services.AdminService;
import sdu.attendancechecker.services.DbService;

public class TeacherModel {

	public static boolean add(int id, String name, String surname,
			String password, String email) {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return false;
		String sql = "INSERT INTO teacher(id, name, surname, password, email) "
				+ "VALUES(?, ?, ?, ?, ?)";
		try {
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setInt(1, id);
			pstmnt.setString(2, name);
			pstmnt.setString(3, surname);
			pstmnt.setString(4, password);
			pstmnt.setString(5, email);
			pstmnt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 
	 * @return
	 */
	public void update() {
		// TODO - implement {class}.{operation}
		throw new UnsupportedOperationException();
	}

	/**
	 * Deletes teacher from 'teacher' table by teacher id
	 * 
	 * @return true id deleted, else false
	 */
	public static boolean delete(int id) {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return false;
		String sql = "DELETE FROM teacher WHERE id = ?";
		try {
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setInt(1, id);
			pstmnt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 
	 * @return
	 */
	public void getById() {
		// TODO - implement {class}.{operation}
		throw new UnsupportedOperationException();
	}

	/**
	 * 
	 * @param email
	 * @param password
	 * @return
	 */

	public static Teacher getByLoginAndPassword(String login, String email,
			String password) {
		try {
			Connection conn = DbService.getConnection();
			if (conn == null) {
				return null;
			}
			String sql = "SELECT * FROM teacher "
					+ "WHERE id = ? and password = ?";
			if (login.equals("email")) {
				sql = "SELECT * FROM teacher "
						+ "WHERE email = ? and password = ?";
			}
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setString(1, email);
			pstmnt.setString(2, password);
			ResultSet result = pstmnt.executeQuery();
			if (result.first()) {
				Teacher teacher = new Teacher();
				teacher.setId(result.getInt("id"));
				teacher.setName(result.getString("name"));
				teacher.setSurname(result.getString("surname"));
				teacher.setPassword(result.getString("password"));
				teacher.setEmail(result.getString("email"));
				return teacher;
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 
	 * @param teacherId
	 * @return Teacher object, otherwise null
	 */
	public static Teacher getByEmail(String email) {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return null;
		String sql = "SELECT * FROM teacher WHERE email = ?";
		try {
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setString(1, email);
			ResultSet result = pstmnt.executeQuery();
			if (result.first()) {
				Teacher teacher = new Teacher();
				teacher.setId(result.getInt("id"));
				teacher.setName(result.getString("name"));
				teacher.setSurname(result.getString("surname"));
				teacher.setPassword(result.getString("password"));
				teacher.setEmail(result.getString("email"));
				return teacher;
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 
	 * @param teacherId
	 * @return Teacher object, otherwise null
	 */
	public static Teacher getById(int teacherId) {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return null;
		String sql = "SELECT * FROM teacher WHERE id = ?";
		try {
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setInt(1, teacherId);
			ResultSet result = pstmnt.executeQuery();
			if (result.first()) {
				Teacher teacher = new Teacher();
				teacher.setId(result.getInt("id"));
				teacher.setName(result.getString("name"));
				teacher.setSurname(result.getString("surname"));
				teacher.setPassword(result.getString("password"));
				teacher.setEmail(result.getString("email"));
				return teacher;
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 
	 * @param email
	 * @param newPassword
	 * @return
	 */

	public static boolean updateEmailById(String newEmail, int id) {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return false;
		String sql = "UPDATE teacher SET email = ? WHERE id = ?";
		try {
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setInt(2, id);
			pstmnt.setString(1, newEmail);
			pstmnt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public static boolean updatePasswordByEmail(String email, String newPassword) {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return false;
		String sql = "UPDATE teacher SET password = ? WHERE email = ?";
		try {
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setString(2, email);
			pstmnt.setString(1, newPassword);
			pstmnt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Return all teachers
	 * 
	 * @return ArrayList<Teacher>, if error return null
	 */
	public static ArrayList<Teacher> getAll() {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return null;
		String sql = "SELECT id, name, surname, password, email FROM teacher";
		ArrayList<Teacher> teacherList = new ArrayList<Teacher>();

		Statement stmnt = null;
		try {
			stmnt = conn.createStatement();
			ResultSet result = stmnt.executeQuery(sql);
			while (result.next()) {
				Teacher t = new Teacher();
				t.setId(result.getInt("id"));
				t.setName(result.getString("name"));
				t.setSurname(result.getString("surname"));
				t.setPassword(result.getString("password"));
				t.setEmail(result.getString("email"));
				teacherList.add(t);
			}
			return teacherList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	
	public static ArrayList<Question> getQuestionsByTeacherId(int id){
		Connection conn = DbService.getConnection();
		if (conn == null)
			return null;
		String sql = "SELECT question_id, question, answer FROM question WHERE teacher_id ="+ id;
		ArrayList<Question> questionList = new ArrayList<Question>();

		Statement stmnt = null;
		try {
			stmnt = conn.createStatement();
			ResultSet result = stmnt.executeQuery(sql);
			while (result.next()) {
				Question q = new Question();
				q.setQuestion_id(result.getInt("question_id"));
				q.setQuestion(result.getString("question"));
				q.setAnswer(result.getString("answer"));
				questionList.add(q);
			}

			return questionList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	public static Question getQuestionById(int id){
		return AdminService.getQuestionById(id);
	}

	public static boolean addQuestionById(int id, String text) {
		Connection conn = DbService.getConnection();
		if (conn == null)
			return false;
		String sql = "INSERT INTO `question` (`question_id`, `teacher_id`, `question`, `answer`) VALUES (NULL, ?, ?, NULL);";
		try {
			PreparedStatement pstmnt = conn.prepareStatement(sql);
			pstmnt.setInt(1, id);
			pstmnt.setString(2, text);
			pstmnt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		
	}
}
